package com.tuanzi.loan.business.repository.sms.impl;

import com.google.common.collect.Lists;
import com.tuanzi.loan.business.constants.sms.SmsCreditCardCondition;
import com.tuanzi.loan.business.constants.sms.SmsUserType;
import com.tuanzi.loan.business.repository.sms.custom.SmsCalculateTaskRepositoryCustom;
import com.tuanzi.loan.business.service.sms.vo.SmsSearchResponse;
import com.tuanzi.loan.core.repository.JOOQBaseRepository;
import com.tuanzi.loan.vo.request.family.SmsCalculateTaskSearchRequest;
import com.tuanzi.loan.vo.request.family.SmsCondition;
import com.tuanzi.loan.vo.response.family.SmsCalculateTaskSearchResponse;
import com.tuanzi.server.loan_base.util.DateUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.jooq.Record;
import org.jooq.RecordMapper;
import org.jooq.impl.DSL;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;

import java.util.List;
import java.util.stream.Collectors;

import static com.tuanzi.loan.generated.jooq.loan.tables.LoanSmsCalculateTask.LOAN_SMS_CALCULATE_TASK;

/**
 * @author Ray 2017/09/21
 */
@Slf4j
public class SmsCalculateTaskRepositoryImpl extends JOOQBaseRepository implements SmsCalculateTaskRepositoryCustom {

	@Override
	protected TableImpl getTable() {
		return LOAN_SMS_CALCULATE_TASK;
	}

	@Override
	public Page<SmsCalculateTaskSearchResponse> list(SmsCalculateTaskSearchRequest searchRequest, Pageable pageable) {
		fields(LOAN_SMS_CALCULATE_TASK.ID, LOAN_SMS_CALCULATE_TASK.NAME, LOAN_SMS_CALCULATE_TASK.STATUS, LOAN_SMS_CALCULATE_TASK.PHONE_COUNT, LOAN_SMS_CALCULATE_TASK.SENT_COUNT,
				LOAN_SMS_CALCULATE_TASK.CREATE_TIME, LOAN_SMS_CALCULATE_TASK.CALCULATE_TIME, LOAN_SMS_CALCULATE_TASK.FINISH_TIME);
		ge(LOAN_SMS_CALCULATE_TASK.PHONE_COUNT, searchRequest.getPhoneCountStart());
		le(LOAN_SMS_CALCULATE_TASK.PHONE_COUNT, searchRequest.getPhoneCountEnd());
		ge(LOAN_SMS_CALCULATE_TASK.SENT_COUNT, searchRequest.getSentCountStart());
		le(LOAN_SMS_CALCULATE_TASK.SENT_COUNT, searchRequest.getSentCountEnd());
		eq(LOAN_SMS_CALCULATE_TASK.STATUS, searchRequest.getStatus());
		contains(LOAN_SMS_CALCULATE_TASK.NAME, searchRequest.getName());
		orderBy(LOAN_SMS_CALCULATE_TASK.CREATE_TIME, Sort.Direction.DESC);
		return search(pageable, new SmsCalculateTaskSearchResponseRecordMapper());
	}

	@Override
	public List<SmsSearchResponse> listSmsUser(SmsCondition request) {
		
		if(CollectionUtils.isEmpty(request.getUserTypes())) {
			return Lists.newArrayList();
		}
		
		if(request.getUserTypes().contains(SmsUserType.REGISTERED_USER) && request.getUserTypes().contains(SmsUserType.CHECKIN_BUT_NOT_REGISTER_USER)) {
			List<SmsSearchResponse> result = listRegisteredUser(request);
			result.addAll(listCheckinButNotRegisterUser(request));
			return result;
		}
		
		// 用户类型 - 注册用户
		if(request.getUserTypes().contains(SmsUserType.REGISTERED_USER)) {
			return listRegisteredUser(request);
		}

		// 用户类型 - 登记未注册用户
		if(request.getUserTypes().contains(SmsUserType.CHECKIN_BUT_NOT_REGISTER_USER)) {
			return listCheckinButNotRegisterUser(request);
		}

		return Lists.newArrayList();
	}

	@Override
	public Long countSmsUser(SmsCondition smsCondition) {
		
		//用户类型 - 注册用户+登记未注册用户
		if(smsCondition.getUserTypes().contains(SmsUserType.REGISTERED_USER) && smsCondition.getUserTypes().contains(SmsUserType.CHECKIN_BUT_NOT_REGISTER_USER)) {
			Long result = countRegisteredUser(smsCondition);
			result += countCheckinButNotRegisterUser(smsCondition);
			return result;
		}
		
		//用户类型 - 注册用户
		if(smsCondition.getUserTypes().contains(SmsUserType.REGISTERED_USER)) {
			return countRegisteredUser(smsCondition);
		}
		
		// 用户类型 - 登记未注册用户
		if (smsCondition.getUserTypes().contains(SmsUserType.CHECKIN_BUT_NOT_REGISTER_USER)) {
			return countCheckinButNotRegisterUser(smsCondition);
		}
		
		return 0L;
	}

	private List<SmsSearchResponse> listRegisteredUser(SmsCondition request) {
		List<Object> parameters = Lists.newArrayList();

		StringBuilder sb = new StringBuilder();
		sb.append(
				"SELECT TB.id, TB.mobnum AS phone, LUI.income, LUI.amount, LUI.marriage, LUI.sex, LUI.social_insurance, LUI.has_car, LUI.has_estate, LUI.idcard, LUI.company, LUI.sesame_point, LUI.credit, LUI.house_fund, LUI.profession, TB.phoneid FROM comm_user AS TB ")
		.append("LEFT JOIN loan_user_info AS LUI ON TB.mobnum = LUI.mobnum ")
		.append("LEFT JOIN `phone_location` PL ON LEFT (TB.mobnum, 7) = PL.`phone` ")
		.append("WHERE TB.`isavaliable` = ? ");
		
		parameters.add(SmsCondition.AVALIABLE);
		
		if (request.getRegisterStartTime() != null) {
			sb.append("AND TB.createtime >= ? ");
			parameters.add(DateUtils.formatDateToYyyyMMdd(request.getRegisterStartTime()));
		}

		if (request.getRegisterEndTime() != null) {
			sb.append("AND TB.createtime <= ? ");
			parameters.add(DateUtils.formatDateToYyyyMMdd(request.getRegisterEndTime()) + " 23:59:59");
		}

		if (CollectionUtils.isNotEmpty(request.getChannels())) {
			sb.append("AND TB.channel IN (?) ");
			parameters.add(DSL.list(request.getChannels().stream().map(DSL::val).collect(Collectors.toList())));
		}

		if (CollectionUtils.isNotEmpty(request.getProducts())) {
			sb.append("AND TB.product_id IN (?) ");
			parameters.add(DSL.list(request.getProducts().stream().map(DSL::val).collect(Collectors.toList())));
		}

		if (StringUtils.isNotBlank(request.getPlatform())) {
			sb.append("AND LUI.platform = ? ");
			parameters.add(request.getPlatform());
		}

		if(request.getHasCredit() != null){

			sb.append(" AND LUI.credit = ? ");

			if(request.getHasCredit().equals(SmsCreditCardCondition.HAS_CREDIT_CARD.ordinal())){ //有信用卡
				parameters.add("是");
			}

			if(request.getHasCredit().equals(SmsCreditCardCondition.NO_CREDIT_CARD.ordinal())){  //无信用卡
				parameters.add("否");
			}

		}

		//地区
		sb.append("AND PL.`province` IN (?) ");
		parameters.add(DSL.list(request.getProvinces().stream().map(DSL::val).collect(Collectors.toList())));


		//运营商
		sb.append("AND PL.`isp` IN (?) ");
		List<String> telcomOperatorNames = getTelecomOperatorNames(request.getTelecomOperators());
		parameters.add(DSL.list(telcomOperatorNames.stream().map(DSL::val).collect(Collectors.toList())));

		//剔除黑名单用户
		sb.append("AND NOT EXISTS (SELECT 1 FROM user_blacklist AS UB WHERE TB.mobnum = UB.mobnum) ");
		
		if(CollectionUtils.isNotEmpty(request.getExcludeCalculateTaskIds())) {
			sb.append("AND NOT EXISTS ( SELECT 1  FROM `loan_sms_task_number` AS LSTN WHERE task_id IN(?) AND LSTN.mobnum = TB.`mobnum`) ");
			parameters.add(DSL.list(request.getExcludeCalculateTaskIds().stream().map(DSL::val).collect(Collectors.toList())));
		}

		if(CollectionUtils.isNotEmpty(request.getElimilateCheckFirstPartis())){
			sb.append(" AND NOT EXISTS( SELECT 1  FROM loan_check_old_user AS LCOU WHERE LCOU.api IN (?) AND LCOU.mobnum = TB.`mobnum`)");
			parameters.add(DSL.list(request.getElimilateCheckFirstPartis().stream().map(DSL::val).collect(Collectors.toList())));
		}

		return dslContext.fetch(sb.toString(), parameters.toArray()).map((Record record) -> {
			SmsSearchResponse response = new SmsSearchResponse();
			response.setId(record.get("id", Long.class));
			response.setPhoneId(record.get("phoneid", String.class));
			response.setPhone(record.get("phone", String.class));
			response.setIncome(record.get("income", Integer.class));
			response.setAmount(record.get("amount", Integer.class));
			response.setSesamePoint(record.get("sesame_point", Integer.class));
			response.setCredit(record.get("credit", String.class));
			response.setHouseFund(record.get("house_fund", String.class));
			response.setProfession(record.get("profession", String.class));
			response.setSex(record.get("sex", String.class));
			response.setMarriage(record.get("marriage", String.class));
			response.setSocialInsurance(record.get("social_insurance", String.class));
			response.setHasCar(record.get("has_car", String.class));
			response.setCompany(record.get("company", String.class));
			response.setHasHouse(record.get("has_estate", String.class));
			response.setIdcard(record.get("idcard", String.class));
			return response;
		});

	}


	/**
	 * 统计注册用户
	 * @param request
	 * @return
	 */
	private Long countRegisteredUser(SmsCondition request) {
		List<Object> parameters = Lists.newArrayList();

		StringBuilder sb = new StringBuilder("SELECT COUNT(1) FROM comm_user AS TB ")
		.append("LEFT JOIN loan_user_info AS LUI ON TB.mobnum = LUI.mobnum ")
		.append("LEFT JOIN `phone_location` PL ON LEFT (TB.mobnum, 7) = PL.`phone` ")
		.append("WHERE TB.`isavaliable` = ? ");
		
		parameters.add(SmsCondition.AVALIABLE);

		if (request.getRegisterStartTime() != null) {
			sb.append("AND TB.createtime >= ? ");
			parameters.add(DateUtils.formatDateToYyyyMMdd(request.getRegisterStartTime()));
		}

		if (request.getRegisterEndTime() != null) {
			sb.append("AND TB.createtime <= ? ");
			parameters.add(DateUtils.formatDateToYyyyMMdd(request.getRegisterEndTime()) + " 23:59:59");
		}

		if (CollectionUtils.isNotEmpty(request.getChannels())) {
			sb.append("AND TB.channel IN (?) ");
			parameters.add(DSL.list(request.getChannels().stream().map(DSL::val).collect(Collectors.toList())));
		}

		if (CollectionUtils.isNotEmpty(request.getProducts())) {
			sb.append("AND TB.product_id IN (?) ");
			parameters.add(DSL.list(request.getProducts().stream().map(DSL::val).collect(Collectors.toList())));
		}

		if (StringUtils.isNotBlank(request.getPlatform())) {
			sb.append("AND LUI.platform = ? ");
			parameters.add(request.getPlatform());
		}

		//地区
		sb.append("AND PL.`province` IN (?) ");
		parameters.add(DSL.list(request.getProvinces().stream().map(DSL::val).collect(Collectors.toList())));

		//运营商
		sb.append("AND PL.`isp` IN (?) ");
		List<String> telcomOperatorNames = getTelecomOperatorNames(request.getTelecomOperators());
		parameters.add(DSL.list(telcomOperatorNames.stream().map(DSL::val).collect(Collectors.toList())));

		sb.append("AND NOT EXISTS (SELECT 1 FROM user_blacklist AS UB WHERE TB.mobnum = UB.mobnum) ");
		
		if(CollectionUtils.isNotEmpty(request.getExcludeCalculateTaskIds())) {
			sb.append("AND NOT EXISTS ( SELECT 1  FROM `loan_sms_task_number` AS LSTN WHERE task_id IN(?) AND LSTN.mobnum = TB.`mobnum`) ");
			parameters.add(DSL.list(request.getExcludeCalculateTaskIds().stream().map(DSL::val).collect(Collectors.toList())));
		}

		if(CollectionUtils.isNotEmpty(request.getElimilateCheckFirstPartis())){
			sb.append(" AND NOT EXISTS( SELECT 1  FROM loan_check_user AS LCU WHERE LCU.api IN (?) AND LCU.status = 0 AND LCU.mobnum = TB.`mobnum`)");
			parameters.add(DSL.list(request.getElimilateCheckFirstPartis().stream().map(DSL::val).collect(Collectors.toList())));
		}

		return dslContext.fetchOne(sb.toString(), parameters.toArray()).into(Long.class);
	}

	/**
	 * 统计登记未注册用户
	 * @param request
	 * @return
	 */
	private Long countCheckinButNotRegisterUser(SmsCondition request) {
		List<Object> parameters = Lists.newArrayList();

		StringBuilder sb = new StringBuilder();
		sb.append("SELECT COUNT(DISTINCT( TB.phone )) FROM common_activity_register_record AS TB ")
		.append("LEFT JOIN `phone_location` PL ON LEFT (TB.phone, 7) = PL.`phone`  WHERE 1 = 1 ");
																																									
		if (request.getRecordStartTime() != null) {
			sb.append("AND TB.cdate >= ? ");
			parameters.add(DateUtils.formatDateToYyyyMMdd(request.getRecordStartTime()));
		}																																										
						
		if (request.getRecordEndTime() != null) {
			sb.append("AND TB.cdate <= ? ");
			parameters.add(DateUtils.formatDateToYyyyMMdd(request.getRecordEndTime()));
		}

		if (CollectionUtils.isNotEmpty(request.getActivityTypes())) {
			sb.append("AND TB.activity_type IN (?) ");
			parameters.add(DSL.list(request.getActivityTypes().stream().map(DSL::val).collect(Collectors.toList())));
		}

		if (CollectionUtils.isNotEmpty(request.getActivityIds())) {
			sb.append("AND TB.activity_id IN (?) ");
			parameters.add(DSL.list(request.getActivityIds().stream().map(DSL::val).collect(Collectors.toList())));
		}

		if (CollectionUtils.isNotEmpty(request.getChannels())) {
			sb.append("AND TB.channel IN (?) ");
			parameters.add(DSL.list(request.getChannels().stream().map(DSL::val).collect(Collectors.toList())));
		}

		if (CollectionUtils.isNotEmpty(request.getProducts())) {
			sb.append("AND TB.prdid IN (?) ");
			parameters.add(DSL.list(request.getProducts().stream().map(DSL::val).collect(Collectors.toList())));
		}

		if (StringUtils.isNotBlank(request.getPlatform())) {
			sb.append("AND TB.platform = ? ");
			parameters.add(request.getPlatform());
		}

		//地区
		sb.append("AND PL.`province` IN (?) ");
		parameters.add(DSL.list(request.getProvinces().stream().map(DSL::val).collect(Collectors.toList())));

		//运营商
		sb.append("AND PL.`isp` IN (?) ");
		List<String> telcomOperatorNames = getTelecomOperatorNames(request.getTelecomOperators());
		parameters.add(DSL.list(telcomOperatorNames.stream().map(DSL::val).collect(Collectors.toList())));

		sb.append("AND NOT EXISTS (SELECT 1 FROM user_blacklist AS UB WHERE TB.phone" +
				" = UB.mobnum) ");
		
		if(CollectionUtils.isNotEmpty(request.getExcludeCalculateTaskIds())) {
			sb.append("AND NOT EXISTS ( SELECT 1  FROM `loan_sms_task_number` AS LSTN WHERE task_id IN(?) AND  LSTN.mobnum = TB.phone) ");
			parameters.add(DSL.list(request.getExcludeCalculateTaskIds().stream().map(DSL::val).collect(Collectors.toList())));
		}

		if(CollectionUtils.isNotEmpty(request.getElimilateCheckFirstPartis())){
			sb.append(" AND NOT EXISTS( SELECT 1  FROM loan_check_user AS LCU WHERE LCU.api IN (?) AND LCU.status = 0 AND LCU.mobnum = TB.`phone`)");
			parameters.add(DSL.list(request.getElimilateCheckFirstPartis().stream().map(DSL::val).collect(Collectors.toList())));
		}
		
		sb.append(" AND NOT EXISTS ( SELECT 1 FROM comm_user AS CU WHERE CU.mobnum = TB.phone )");

		return dslContext.fetchOne(sb.toString(), parameters.toArray()).into(Long.class);
	}


	private List<SmsSearchResponse> listCheckinButNotRegisterUser(SmsCondition request) {
		List<Object> parameters = Lists.newArrayList();

		StringBuilder sb = new StringBuilder();
		sb.append("SELECT DISTINCT( TB.phone ) AS phone FROM common_activity_register_record AS TB ")
		.append("LEFT JOIN `phone_location` PL ON LEFT (TB.phone, 7) = PL.`phone` WHERE 1 = 1 ");

		if (request.getRecordStartTime() != null) {
			sb.append("AND TB.cdate >= ? ");
			parameters.add(DateUtils.formatDateToYyyyMMdd(request.getRecordStartTime()));
		}

		if (request.getRecordEndTime() != null) {
			sb.append("AND TB.cdate <= ? ");
			parameters.add(DateUtils.formatDateToYyyyMMdd(request.getRecordEndTime()));
		}

		if (CollectionUtils.isNotEmpty(request.getActivityTypes())) {
			sb.append("AND TB.activity_type IN (?) ");
			parameters.add(DSL.list(request.getActivityTypes().stream().map(DSL::val).collect(Collectors.toList())));
		}

		if (CollectionUtils.isNotEmpty(request.getActivityIds())) {
			sb.append("AND TB.activity_id IN (?) ");
			parameters.add(DSL.list(request.getActivityIds().stream().map(DSL::val).collect(Collectors.toList())));
		}

		if (CollectionUtils.isNotEmpty(request.getChannels())) {
			sb.append("AND TB.channel IN (?) ");
			parameters.add(DSL.list(request.getChannels().stream().map(DSL::val).collect(Collectors.toList())));
		}

		if (CollectionUtils.isNotEmpty(request.getProducts())) {
			sb.append("AND TB.prdid IN (?) ");
			parameters.add(DSL.list(request.getProducts().stream().map(DSL::val).collect(Collectors.toList())));
		}

		if (StringUtils.isNotBlank(request.getPlatform())) {
			sb.append("AND TB.platform = ? ");
			parameters.add(request.getPlatform());
		}

		if(request.getHasCredit() != null){
			sb.append("AND TB.if_credit_card = ? ");
			parameters.add(request.getHasCredit());
		}

		//地区
		sb.append("AND PL.`province` IN (?) ");
		parameters.add(DSL.list(request.getProvinces().stream().map(DSL::val).collect(Collectors.toList())));

		//运营商
		sb.append("AND PL.`isp` IN (?) ");
		List<String> telcomOperatorNames = getTelecomOperatorNames(request.getTelecomOperators());
		parameters.add(DSL.list(telcomOperatorNames.stream().map(DSL::val).collect(Collectors.toList())));

		sb.append("AND NOT EXISTS (SELECT 1 FROM user_blacklist AS UB WHERE TB.phone = UB.mobnum) ");
		
		if(CollectionUtils.isNotEmpty(request.getExcludeCalculateTaskIds())) {
			sb.append("AND NOT EXISTS ( SELECT 1  FROM `loan_sms_task_number` AS LSTN WHERE task_id IN(?) AND  LSTN.mobnum = TB.phone) ");
			parameters.add(DSL.list(request.getExcludeCalculateTaskIds().stream().map(DSL::val).collect(Collectors.toList())));
		}

		if(CollectionUtils.isNotEmpty(request.getElimilateCheckFirstPartis())){
			sb.append(" AND NOT EXISTS( SELECT 1  FROM loan_check_user AS LCU WHERE LCU.api IN (?) AND LCU.status = 0 AND LCU.mobnum = TB.`phone`)");
			parameters.add(DSL.list(request.getElimilateCheckFirstPartis().stream().map(DSL::val).collect(Collectors.toList())));
		}
		
		sb.append(" AND NOT EXISTS ( SELECT 1 FROM comm_user AS CU WHERE CU.mobnum = TB.phone ) ");
		
		return dslContext.fetch(sb.toString(), parameters.toArray()).map((Record record) -> {
			SmsSearchResponse response = new SmsSearchResponse();
			response.setPhone(record.get("phone", String.class));
			return response;
		});
	}
	

	private final class SmsCalculateTaskSearchResponseRecordMapper implements RecordMapper<Record, SmsCalculateTaskSearchResponse> {

		@Override
		public SmsCalculateTaskSearchResponse map(Record record) {
			SmsCalculateTaskSearchResponse response = new SmsCalculateTaskSearchResponse();
			response.setId(record.get(LOAN_SMS_CALCULATE_TASK.ID));
			response.setName(record.get(LOAN_SMS_CALCULATE_TASK.NAME));
			response.setStatus(record.get(LOAN_SMS_CALCULATE_TASK.STATUS));
			response.setPhoneCount(record.get(LOAN_SMS_CALCULATE_TASK.PHONE_COUNT));
			response.setSentCount(record.get(LOAN_SMS_CALCULATE_TASK.SENT_COUNT));
			response.setCreateTime(record.get(LOAN_SMS_CALCULATE_TASK.CREATE_TIME));
			response.setCalculateTime(record.get(LOAN_SMS_CALCULATE_TASK.CALCULATE_TIME));
			response.setFinishTime(record.get(LOAN_SMS_CALCULATE_TASK.FINISH_TIME));
			return response;
		}
	}


	private List<String> getTelecomOperatorNames(List<String> telcomOperators){
		List<String> telecomOperatorNames = Lists.newArrayList();
		if(telcomOperators.contains("0")){
			telecomOperatorNames.add("移动");
		}

		if(telcomOperators.contains("1")){
			telecomOperatorNames.add("联通");
		}

		if(telcomOperators.contains("2")){
			telecomOperatorNames.add("电信");
		}
		return telecomOperatorNames;
	}

}
